package com.ruoyi.recycle.mapper;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.ruoyi.recycle.domain.ClientUserManagement;
import com.ruoyi.recycle.dto.HomePageStatisticalClientData;
import com.ruoyi.recycle.dto.HomePageStatisticalData;
import com.ruoyi.recycle.dto.SubordinateDTO;
import com.ruoyi.recycle.vo.AttractDataVO;
import com.ruoyi.recycle.vo.AttractRecordVO;
import com.ruoyi.recycle.vo.ShopAttractVO;
import com.ruoyi.recycle.vo.SubordinateVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 普通用户管理Mapper接口
 *
 * @author Wubk
 * @date 2022-05-21
 */
public interface ClientUserManagementMapper extends BaseMapper<ClientUserManagement> {
    /**
     * 查询普通用户管理
     *
     * @param id 普通用户管理主键
     * @return 普通用户管理
     */
    public ClientUserManagement selectClientUserManagementById(Long id);

    /**
     * 查询普通用户管理列表
     *
     * @param clientUserManagement 普通用户管理
     * @return 普通用户管理集合
     */
    public List<ClientUserManagement> selectClientUserManagementList(ClientUserManagement clientUserManagement);

    /**
     * 新增普通用户管理
     *
     * @param clientUserManagement 普通用户管理
     * @return 结果
     */
    public int insertClientUserManagement(ClientUserManagement clientUserManagement);

    /**
     * 修改普通用户管理
     *
     * @param clientUserManagement 普通用户管理
     * @return 结果
     */
    public int updateClientUserManagement(ClientUserManagement clientUserManagement);

    /**
     * 删除普通用户管理
     *
     * @param id 普通用户管理主键
     * @return 结果
     */
    public int deleteClientUserManagementById(Long id);

    /**
     * 批量删除普通用户管理
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteClientUserManagementByIds(Long[] ids);

    /**
     * 首页统计用户回收产生积分与用户情况
     *
     * @return
     */
    HomePageStatisticalData homePageStatisticalData();

    List<HomePageStatisticalClientData> homePageStatisticalClientData(@Param("stateTime") Date stateTime, @Param("type") String type);

    List<HomePageStatisticalClientData> mapTrajectoryStatistics(@Param("stateTime") Date stateTime, @Param("type") String type);

    @Select("select id from client_user_management ${ew.customSqlSegment}")
    Map<String, List<ClientUserManagement>> clientGroupBySalesman(@Param(Constants.WRAPPER) LambdaQueryWrapper queryWrapper);

    Integer selectSubordinateOrderCount(@Param("clientId") Integer clientId);

    List<SubordinateVO> mySubordinate(SubordinateDTO subordinateDTO);


    @Select("select shopId, shopName, salesmanId,salesman, attractDay " +
            "FROM (select  c.shop_id as shopId, s.salesman_id as salesmanId,s.salesman_name as salesman,s.shop_name as shopName,cl.attract_count as attractDay  FROM (select shop_id,COUNT(id) as attract_count FROM client_user_management ${ew.customSqlSegment}) cl LEFT JOIN client_user_management c on cl.shop_id = c.shop_id" +
            " LEFT JOIN shop_management s on c.shop_id = s.id ORDER BY s.created_time) attract")
    List<AttractRecordVO> countShopAttract(@Param(Constants.WRAPPER) LambdaQueryWrapper<ClientUserManagement> queryWrapper);

    @Select("select clientId, clientName, salesmanId,salesman, attractDay " +
            "FROM (select  c.id as clientId, c.user_name as clientName,  s.salesman_id as salesmanId,s.salesman_name as salesman,cl.attract_count as attractDay  FROM (select superior_id,COUNT(id) as attract_count FROM client_user_management ${ew.customSqlSegment}) cl LEFT JOIN client_user_management c on cl.superior_id = c.id" +
            " LEFT JOIN shop_management s on c.shop_id = s.id ORDER BY cl.created_time) attract")
    List<AttractRecordVO> countClientAttract(@Param(Constants.WRAPPER) LambdaQueryWrapper<ClientUserManagement> queryWrapper);


    @Select("select shop_id as shopId, group_concat(id) FROM client_user_management WHERE shop_id = ${shopIds}")
    ShopAttractVO groupClientByShop(@Param("shopId") Long shopId);

    @Select("<script>" + "select COUNT(id) as attractCount," +
            "DATE_FORMAT(created_time,'%Y-%m-%d') as date " +
            "FROM client_user_management where id in " +
            "   <foreach item='item' index='index' collection='clientIds' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "   </foreach>" +
            "group by date order by date DESC" +
            "</script>")
    List<AttractDataVO> shopAttractGroupByDay(@Param("clientIds") List<Long> clientIds);

    @Select("<script>" + "select " +
            "COUNT(id) as attractCount," +
            "DATE_FORMAT(created_time,'%Y-%m') as date " +
            "FROM client_user_management " +
            "where id in " +
            "   <foreach item='item' index='index' collection='clientIds' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "   </foreach>" +
            "group by date order by date DESC" +
            "</script>")
    List<AttractDataVO> shopAttractGroupByMonth(@Param("clientIds") List<Long> clientIds);


    @Select("select COUNT(id) as attractCount,DATE_FORMAT(created_time,'%Y-%m-%d') as date FROM client_user_management ${ew.customSqlSegment}")
    List<AttractDataVO> clientAttractGroupByDay(@Param(Constants.WRAPPER) LambdaQueryWrapper<ClientUserManagement> wrapper);

    @Select("select COUNT(id) as attractCount,DATE_FORMAT(created_time,'%Y-%m') as date FROM client_user_management ${ew.customSqlSegment}")
    List<AttractDataVO> clientAttractGroupByMonth(@Param(Constants.WRAPPER) LambdaQueryWrapper<ClientUserManagement> wrapper);


}
